import { Injectable } from '@nestjs/common';
import { Post, Prisma, } from '@prisma/client';
import { PrismaService } from 'src/common/prisma/prisma.service';
import { RawPostWithUser ,FormattedPostWithUser} from './interfaces/posts.dto';

@Injectable()
export class PostsService {
    constructor(private prisma: PrismaService) { }

    async findAll(): Promise<Post[]> {
        return this.prisma.post.findMany({
            include: {
                author: true,
            }
        });
    }

    async getAllPostsWithRawQuery(): Promise<FormattedPostWithUser[]> {
        const sql = Prisma.sql`
            SELECT 
                posts.id AS postId,
                posts.title,
                posts.content,
                posts.createdAt AS postCreatedAt,
                posts.updatedAt AS postUpdatedAt,
                posts.authorId,
                users.id AS userId,
                users.username,
                users.email,
                users.createdAt AS userCreatedAt
            FROM Post posts
            LEFT JOIN User users ON posts.authorId = users.id
        `;

        const rawResult=  await this.prisma.$queryRaw<RawPostWithUser[]>(sql);

        return  rawResult.map((item) => ({
            id: item.postId,
            title: item.title,
            content: item.content,
            createdAt: item.postCreatedAt,
            updatedAt: item.postUpdatedAt,
            authorId: item.authorId,
            author: {
                id: item.userId,
                username: item.username,
                email: item.email,
                createdAt: item.userCreatedAt,
            },
        }))
    }

    async getPostWithRawQuery(id: number): Promise<Post> {
        const sql = Prisma.sql`
                SELECT
                posts.id AS postId,
                posts.title,
                posts.content,
                posts.createdAt AS postCreatedAt,
                posts.updatedAt AS postUpdatedAt,
                posts.authorId,
                users.id AS userId,
                users.username,
                users.email,
                users.createdAt AS userCreatedAt
            FROM Post posts
            LEFT JOIN User users ON posts.authorId = users.id
            WHERE posts.id = ${id}
        `;

        const rawResultPost = await this.prisma.$queryRaw<RawPostWithUser[]>(sql);

        return rawResultPost.map((item) => ({
            id: item.postId,
            title: item.title,
            content: item.content,
            createdAt: item.postCreatedAt,
            updatedAt: item.postUpdatedAt,
            authorId: item.authorId,
            author: {
                id: item.userId,
                username: item.username,
                email: item.email,
                createdAt: item.userCreatedAt,
            },
        }))[0];
    }

    async getPostById(id: number,userId: number): Promise<Post[]> {
        const sql = Prisma.sql`
            SELECT * FROM Post WHERE id = ${id} AND authorId = ${userId}
        `;
        return this.prisma.$queryRaw(sql);
    }
}